#!/bin/sh
#
# Send out reminder emails to people who have not had mail for some time
# Current limit: 1 year....
#
# Change 2002-04-05: Added machine-only registrations.
#    At this moment, empty registrations are all frozen.
#    Have to make that a feature, not a bug....
#

DAYS=365
DATE=`perl -e "use HTTP::Date;print HTTP::Date::time2iso(time()-$DAYS*86400)"`;
#

# Drop temp table - even if it is not present, don't stop
echo "drop table temp_reminders" | mysql -h ${COUNTER_DBHOST-localhost} counter -u counter -ppassword;

# Select to temp table
mysql -h ${COUNTER_DBHOST-localhost} counter -u counter -ppassword <<EoF | tail +2 | sed -e 's/^/sendemail /'
create table temp_reminders (
f_key int,
country char(2),
name varchar(64),
email varchar(64)
);

insert into temp_reminders select users.f_key, persons.country, persons.name, users.email
 from users left join persons on users.f_key = persons.f_key, email
 where
     emailtime < '$DATE'
 and users.f_ctime < '$DATE'
 and users.logintime < '$DATE'
 and users.email = email.f_key
 and users.state <> 'blocked'
 and users.state <> 'frozen'
 and email.state = 'ok';

select f_key, "#", country, name, email from temp_reminders
 order by country, name
 limit ${1-10};

EoF
